package sql

import (
	"database/sql"
	"fmt"

	// 导入包，导入前缀为下划线，则init函数被执行，然后注册驱动。
	_ "github.com/mattn/go-sqlite3"
)

var db *sql.DB
var err error

// 连接数据库
func ConnectDB() {
	// Open() 函数指定驱动名称和数据源名称
	db, err = sql.Open("sqlite3", "data.db")
	if err != nil {
		fmt.Printf("Database creation failed: %v\n", err)
		return
	}
	// 调用db.Close() 函数，确保关闭数据库并阻止启动新的查询
	// defer db.Close()

	var version string
	// QueryRow() 执行查询，返回版本号
	err = db.QueryRow("SELECT SQLITE_VERSION()").Scan(&version)
	if err != nil {
		fmt.Printf("Database creation failed: %v\n", err)
		return
	}
	// 连接成功，打印出"database connected：版本号"
	fmt.Printf("Database creation successful: %v\n", version)
}

// 插入数据
func Insert(sql string, params []interface{}) {
	// 插入语句
	// res, err := db.Exec("INSERT INTO express_orders(uid, weight) VALUES(?,?)", uid, weight)
	/* values := []interface{}{uid, name}
	str := "SELECT weight FROM express_orders WHERE uid = %d and name=%s" */
	sql = fmt.Sprintf(sql, params...)
	fmt.Println(sql)
	res, err := db.Exec(sql)
	if err != nil {
		fmt.Printf("Insert data failed: %v\n", err)
		return
	}

	// 获取自增ID
	lastInsertId, _ := res.LastInsertId()
	fmt.Printf("Successfully inserted data, lastInsertId =  %v\n", lastInsertId)
}

// 删除数据
func Delete() {
	ret, err := db.Exec("delete from user where uid=?;", 12)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}

	n, err := ret.RowsAffected()
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}

// 更新数据
func UpdateRow(sql string, params []interface{}) {
	// ret, err := db.Exec("update user set name=? where uid=?;", "tt", 12)
	sql = fmt.Sprintf(sql, params...)
	ret, err := db.Exec(sql)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}

	n, err := ret.RowsAffected()
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

type List struct {
	Id      int
	Name    string
	Appid   string
	Version string
	Temp_id string
	Desc    string
}

// 查询数据
func Query(sql string, params ...[]interface{}) (result []List) {

	// 结果集合
	result = make([]List, 0)

	// 查询语句
	// rows, err := db.Query("SELECT weight FROM express_orders WHERE uid = ?", uid)

	/* uid := 123
	name := "张三"
	values := []interface{}{uid, name}
	str := "SELECT weight FROM express_orders WHERE uid = %d and name=%s"
	*/
	/* formattedStr := fmt.Sprintf(sql, params...)
	fmt.Println(formattedStr) */

	sqlStatement := sql
	/* fmt.Println(len(params))
	if len(params) > 0 {
		sqlStatement = fmt.Sprintf(sql, params...)
	} */

	rows, err := db.Query(sqlStatement)
	if err != nil {
		fmt.Printf("Failed to query data: %v\n", err)
		return
	}
	for rows.Next() {
		var id int
		var name string
		var appid string
		var version string
		var temp_id string
		var desc string
		err = rows.Scan(&id, &name, &appid, &version, &temp_id, &desc)
		if err != nil {
			fmt.Printf("Failed to read data: %v\n", err)
			continue
		}
		// weightList = append(weightList, weight)
		result = append(result, List{Id: id, Name: name, Appid: appid, Version: version, Temp_id: temp_id, Desc: desc})
	}
	return result
}
